<?php
class M_stockin extends CI_Model
{
  public function __construct()
  {
    parent::__construct();
  }

  function _get_stock($user_id, $day)
  {
    //cari cluster CC yg sedang login
    $query = $this->db->query("select f_get_cluster('".$user_id."')");
    $cluster = $query->row()->f_get_cluster;

    //cari data outlet yang memiliki stock in dalam 7 hari kebelakang
    $query = $this->db->query("select date(h.created_on) as tanggal
      , h.channel_id as id_partner
      , f_get_channel_name(h.channel_id) as nama_partner
      , imap.item_name
      , count(imap.item_code) as jumlah_item
      , d.stock_scan_id
      from t_trx_stock_scan h
      join t_trx_stock_scan_detail d
       on h.stock_scan_id = d.stock_scan_id
      join t_mtr_item itm
       on d.iccid = itm.iccid
      join t_mtr_item_mapping imap
       on itm.item_mapping_id = imap.item_mapping_id
      join t_mtr_territory tt
       on tt.territory_id = f_get_territory_id_by_channel_id(h.channel_id)
      where tt.territory_id is not null
       and tt.parent_id = ".$cluster."
      and date(h.created_on) BETWEEN date(now())-".$day." and CURRENT_DATE
      GROUP BY d.stock_scan_id, h.channel_id, imap.item_code, imap.item_name, date(h.created_on)
      ORDER BY h.channel_id ASC, date(h.created_on) DESC");

      // ORDER BY date(h.created_on) DESC, h.channel_id ASC");

    //return an array containiong territory_id and outlet_name
    return $query->result();
  }

  function _get_stock_detail($user_id, $id_partner, $stock_scan_id, $day)
  {
    //cari cluster CC yg sedang login
    $query = $this->db->query("select f_get_cluster('".$user_id."')");
    $cluster = $query->row()->f_get_cluster;

    $query = $this->db->query(
      "select ss.stock_scan_id, 
       f_get_channel_name(ss.channel_id) as partner_name,
       u.user_name as created_by,
       ss.created_on,
       ssd.iccid,
       imap.item_name
       from t_trx_stock_scan ss
       join t_trx_stock_scan_detail ssd on ss.stock_scan_id = ssd.stock_scan_id
       join t_mtr_territory tt on tt.territory_id = f_get_territory_id_by_channel_id(ss.channel_id)
       join t_mtr_user u on ss.created_by = u.user_id
       join t_mtr_item itm on ssd.iccid = itm.iccid
       join t_mtr_item_mapping imap on itm.item_mapping_id = imap.item_mapping_id
       where tt.territory_id is not null
       and tt.parent_id = ".$cluster."
      and ss.channel_id = '".$id_partner."'
      and ssd.stock_scan_id = '".$stock_scan_id."'
      and date(ss.created_on) BETWEEN date(now())-".$day." and CURRENT_DATE"
    );

    return $query->result();
  }
} 
